if exists (select * from dbo.sysobjects where name='sp_PhanCongNhanVien')
begin
	drop proc sp_PhanCongNhanVien
end
go
create proc sp_PhanCongNhanVien
	@MADA 	int,
	@TENDA	nvarchar(30),
	@DDIEM	nvarchar(30),
	@MA_phg	int
as
begin
	if not exists (select * from DEAN where @MADA=MADA)
		begin
			insert into DEAN values (@MADA,@TENDA,@DDIEM,@MA_phg)
			 
			declare @MANV_temp int
			declare @TENNV_temp nvarchar(30)
			declare @DEM_temp nvarchar(30)
			declare @C cursor
			set @C = cursor for  
			select top 3  A.MANV,A.TENNV, count (B.MA_NVIEN) as 'dem'
			from NHANVIEN as A left join PHANCONG as B on A.MANV=B.MA_NVIEN
			group by A.MANV,A.TENNV
			order by dem,A.TENNV
		open @C
		FETCH NEXT FROM @C into @MANV_temp,@TENNV_temp,@DEM_temp
			while(@@fetch_status=0)
				begin
					insert into phancong values(@MANV_temp,@MADA,NULL)	
					print	'da phan cong de an x'
						+	cast(@MADA as nvarchar(10))
						+	'cho nhan vien'
						+	@MANV_temp   +'thanh cong'
			
					FETCH NEXT FROM @C into @MANV_temp,@TENNV_temp,@DEM_temp
					
				end
		close @C	
		DEALLOCATE @C
	END
end


